﻿Imports System.Data.OleDb
Imports System.Web.Security
Imports System.Data.SqlClient
Imports System.Web.HttpUtility

Module DatabaseModule
    Dim dbc As OleDb.OleDbConnection = New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

    Function getCurrentUserId() As Object
        Return Membership.GetUser(HttpContext.Current.User.Identity.Name).ProviderUserKey
    End Function

    Function userLoggedIn()
        Return HttpContext.Current.User.Identity.IsAuthenticated
    End Function

    Function GetData(ByVal queryString As String) As DataSet

        ' Retrieve the connection sting stored in the Web.config file.
        
        Dim ds As New DataSet()
        Dim adapter As New OleDbDataAdapter(queryString, dbc)
        dbc.Open()
        ' Fill the DataSet.
        adapter.Fill(ds)
        dbc.Close()

        Return ds

    End Function

    'Horrible, horrble, horrible. Ought to retun a data structure
    Function getUserBooksTable() As String

        'Safe to use concat here since userId is not touched by (evil) user input
        Dim bookSearch As String = "SELECT Book.ISBN, Book.Title, Book.Description, Book.year_published AS Published, Author.name AS Author, BookShelf.[order] FROM ((Book INNER JOIN Author ON Book.author_id = Author.ID) INNER JOIN BookShelf ON Book.ISBN = BookShelf.ISBN) WHERE (BookShelf.user_id = " & CStr(getCurrentUserId()) & ")"

        Dim ds As DataSet = GetData(bookSearch)
        Dim dr As DataRow

        Dim i As Int32 = 0
        Dim tableHTML As String = "<table><thead><tr> <th>Title</th> <th>Author</th> <th>Year Published</th> <th>ISBN</th></tr></thead><tbody>"



        For Each dr In ds.Tables(0).Rows
            Dim isbn As String = dr("ISBN")
            Dim detailUrl As String = "/details.aspx?b=" + UrlEncode(isbn)

            tableHTML = tableHTML + "<td><a href=""" + detailUrl + """>" + dr("Title") + "</a></td>"
            tableHTML = tableHTML + "<td>" + dr("Author") + "</td>"
            tableHTML = tableHTML + "<td>" + CStr(dr("Published")) + "</td>"
            tableHTML = tableHTML + "<td>" + dr("ISBN") + "</td></tr>"

        Next

        Return tableHTML + "</tbody></table>"
    End Function

    Function addBookToUsersShelf(ISBN As String)
        removeBookFromUsersShelf(ISBN)
        Dim UserID As String = getCurrentUserId()
        Dim bookAddition As String = "INSERT INTO BookShelf (user_id, isbn ) VALUES (@f1, @f2)"
        Dim cmd As OleDbCommand = New OleDbCommand(bookAddition, dbc)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", UserID)
        cmd.Parameters.AddWithValue("@f2", ISBN)

        dbc.Open()
        cmd.ExecuteNonQuery()
        dbc.Close()
        Return Nothing
    End Function

    Function removeBookFromUsersShelf(ISBN As String)
        Dim UserID As String = getCurrentUserId()
        Dim bookRemoval As String = "DELETE FROM BookShelf WHERE user_id=@f1 AND isbn=@f2"
        Dim cmd As OleDbCommand = New OleDbCommand(bookRemoval, dbc)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", UserID)
        cmd.Parameters.AddWithValue("@f2", ISBN)
        dbc.Open()
        cmd.ExecuteNonQuery()
        dbc.Close()
        Return Nothing
    End Function

    Function isBookOnUsersShelf(ISBN As String)
        Dim bookIsOnUsersShelf As Boolean
        Dim UserID As String = getCurrentUserId()
        Dim bookRemoval As String = "SELECT order FROM BookShelf WHERE user_id=@f1 AND isbn=@f2"
        Dim cmd As OleDbCommand = New OleDbCommand(bookRemoval, dbc)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", UserID)
        cmd.Parameters.AddWithValue("@f2", ISBN)
        dbc.Open()
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        bookIsOnUsersShelf = reader.HasRows

        dbc.Close()
        Return bookIsOnUsersShelf
    End Function

    Function getBookTitle(id As String) As String

        Dim retVal As String

        Dim queryString As String = "SELECT title FROM Book WHERE ISBN = @f1"
        Dim title As String
        Dim command As New OleDbCommand(queryString, dbc)
        command.Parameters.AddWithValue("@f1", id)
        dbc.Open()
        Dim reader As OleDbDataReader = command.ExecuteReader()

        If reader.HasRows Then
            reader.Read()
            title = reader.GetString(0)

            reader.Close()

            retVal = title
        Else
            retVal = ""
        End If

        dbc.Close()

        Return retVal

    End Function

    Function getBookYearPublished(id As String) As String

        Dim retVal As String

        Dim queryString As String = "SELECT year_published FROM Book WHERE ISBN = @f1"
        Dim year_published As String
        Dim command As New OleDbCommand(queryString, dbc)
        command.Parameters.AddWithValue("@f1", id)
        dbc.Open()
        Dim reader As OleDbDataReader = command.ExecuteReader()

        If reader.HasRows Then
            reader.Read()
            year_published = CStr(reader.GetInt32(0))

            reader.Close()

            retVal = year_published
        Else
            retVal = ""
        End If

        dbc.Close()

        Return retVal

    End Function

    Function getBookISBN(id As String) As String

        Dim retVal As String

        Dim queryString As String = "SELECT ISBN FROM Book WHERE ISBN = @f1"
        Dim ISBN As String
        Dim command As New OleDbCommand(queryString, dbc)
        command.Parameters.AddWithValue("@f1", id)
        dbc.Open()
        Dim reader As OleDbDataReader = command.ExecuteReader()

        If reader.HasRows Then
            reader.Read()
            ISBN = reader.GetString(0)

            reader.Close()

            retVal = ISBN
        Else
            retVal = ""
        End If

        dbc.Close()

        Return retVal

    End Function

    Function getBookDescription(id As String) As String

        Dim retVal As String

        Dim queryString As String = "SELECT description FROM Book WHERE ISBN = @f1"
        Dim Description As String
        Dim command As New OleDbCommand(queryString, dbc)
        command.Parameters.AddWithValue("@f1", id)
        dbc.Open()
        Dim reader As OleDbDataReader = command.ExecuteReader()

        If reader.HasRows Then
            reader.Read()
            Description = reader.GetString(0)

            reader.Close()

            retVal = Description
        Else
            retVal = ""
        End If

        dbc.Close()

        Return retVal
    End Function

    Function getBookPicture(id As String) As String

        Dim retVal As String

        Dim queryString As String = "SELECT photo FROM Book WHERE ISBN = @f1"
        Dim Picture As String
        Dim command As New OleDbCommand(queryString, dbc)
        command.Parameters.AddWithValue("@f1", id)
        dbc.Open()
        Dim reader As OleDbDataReader = command.ExecuteReader()

        If reader.HasRows Then
            reader.Read()
            Picture = reader.GetString(0)

            reader.Close()

            retVal = Picture
        Else
            retVal = ""
        End If

        dbc.Close()

        Return retVal

    End Function

    Function getBookAuthor(id As String) As String

        Dim retVal As String

        Dim queryString As String = "SELECT Author.name FROM (Author INNER JOIN Book ON Author.ID = Book.author_id) WHERE (Book.ISBN = @f1)"
        Dim author As String
        Dim command As New OleDbCommand(queryString, dbc)
        command.Parameters.AddWithValue("@f1", id)
        dbc.Open()
        Dim reader As OleDbDataReader = command.ExecuteReader()

        If reader.HasRows Then
            reader.Read()
            author = reader.GetString(0)

            reader.Close()

            retVal = author
        Else
            retVal = ""
        End If

        dbc.Close()

        Return retVal

    End Function

    Function getAuthorID(authorName As String) As Int32

        Dim retVal As Int32

        Dim queryString As String = "SELECT ID FROM Author WHERE name = @f1"
        Dim authorId As String
        Dim command As New OleDbCommand(queryString, dbc)
        command.Parameters.AddWithValue("@f1", authorName)
        dbc.Open()
        Dim reader As OleDbDataReader = command.ExecuteReader()

        If reader.HasRows Then
            reader.Read()
            authorId = reader.GetInt32(0)

            reader.Close()

            retVal = authorId
        Else
            retVal = -1
        End If

        dbc.Close()

        Return retVal

    End Function

    Function addNewAuthor(authorName As String)
        Dim authorAddition As String = "INSERT INTO Author (name) VALUES (@f1)"
        Dim cmd2 As OleDbCommand = New OleDbCommand(authorAddition, dbc)
        cmd2.CommandType = CommandType.Text
        cmd2.Parameters.AddWithValue("@f1", authorName)
        dbc.Open()
        cmd2.ExecuteNonQuery()
        dbc.Close()
        Return Nothing
    End Function

    Function addNewBook(ISBN As String, Title As String, Description As String, authorID As Integer, yearPublished As String, pictureFileName As String)
        Dim bookAddition As String = "INSERT INTO Book (Title, ISBN, Description, author_id, year_published, photo) VALUES (@f1, @f2, @f3, @f4, @f5, @f6)"
        Dim cmd As OleDbCommand = New OleDbCommand(bookAddition, dbc)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", Title)
        cmd.Parameters.AddWithValue("@f2", ISBN)
        cmd.Parameters.AddWithValue("@f3", Description)
        cmd.Parameters.AddWithValue("@f4", authorID.ToString())
        cmd.Parameters.AddWithValue("@f5", yearPublished)
        cmd.Parameters.AddWithValue("@f6", "pictures/" & pictureFileName)
        dbc.Open()
        cmd.ExecuteNonQuery()
        dbc.Close()
        Return Nothing
    End Function

    Function getRating(ISBN As String)

        Dim UserID As String = getCurrentUserId()
        Dim retVal As Int32

        Dim queryString As String = "SELECT Rating FROM Rating WHERE ISBN = @f1 AND UserId = @f2"
        Dim command As New OleDbCommand(queryString, dbc)
        command.Parameters.AddWithValue("@f1", ISBN)
        command.Parameters.AddWithValue("@f2", UserID)
        dbc.Open()
        retVal = command.ExecuteScalar()

        dbc.Close()

        Return retVal
    End Function

    Function getAverageRating(ISBN As String)

        Dim retVal As Int32 = 0
        Dim queryString As String = "SELECT COUNT(Rating) FROM Rating WHERE ISBN = @f1"
        Dim command As New OleDbCommand(queryString, dbc)

        command.Parameters.AddWithValue("@f1", ISBN)
        dbc.Open()
        Dim count As Integer = command.ExecuteScalar()
        If (count > 0) Then
            queryString = "SELECT AVG(Rating) AS AverageRating FROM Rating WHERE ISBN = @f1"
            command = New OleDbCommand(queryString, dbc)

            command.Parameters.AddWithValue("@f1", ISBN)

            retVal = command.ExecuteScalar()
        End If
        dbc.Close()

        Return retVal
    End Function

    Function removeRating(ISBN As String)
        Dim UserID As String = getCurrentUserId()
        Dim ratingRemoval As String = "DELETE FROM Rating WHERE UserID=@f1 AND ISBN=@f2"
        Dim cmd As OleDbCommand = New OleDbCommand(ratingRemoval, dbc)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", UserID)
        cmd.Parameters.AddWithValue("@f2", ISBN)
        dbc.Open()
        cmd.ExecuteNonQuery()
        dbc.Close()
        Return Nothing
    End Function

    Function setRating(ISBN As String, Rating As Integer)
        removeRating(ISBN)
        Dim UserID As String = getCurrentUserId()
        Dim ratingAddition As String = "INSERT INTO Rating (UserID, ISBN, Rating) VALUES (@f1, @f2, @f3)"
        Dim cmd As OleDbCommand = New OleDbCommand(ratingAddition, dbc)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", UserID)
        cmd.Parameters.AddWithValue("@f2", ISBN)
        cmd.Parameters.AddWithValue("@f3", Rating)
        dbc.Open()
        cmd.ExecuteNonQuery()
        dbc.Close()
        Return Nothing
    End Function


    Function addComment(isbn As String, body As String)


        Dim UserID As String = getCurrentUserId()
        Dim ratingAddition As String = "INSERT INTO BookComment (user_id, isbn, body) VALUES (@f1, @f2, @f3)"
        Dim cmd As OleDbCommand = New OleDbCommand(ratingAddition, dbc)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", UserID)
        cmd.Parameters.AddWithValue("@f2", isbn)
        cmd.Parameters.AddWithValue("@f3", body)
        dbc.Open()
        cmd.ExecuteNonQuery()
        dbc.Close()
        Return Nothing

    End Function

    Function getComments(isbn As String) As String

        Dim retVal As String = "<div class=""comments"">"

        Dim queryString As String = "SELECT aspnet_Users.UserName AS username, BookComment.body, BookComment.[time] FROM (BookComment INNER JOIN aspnet_Users ON BookComment.user_id = aspnet_Users.UserId) WHERE (BookComment.isbn = @f1) ORDER BY BookComment.[time] DESC"
        Dim command As New OleDbCommand(queryString, dbc)
        command.Parameters.AddWithValue("@f1", isbn)
        dbc.Open()
        Dim reader As OleDbDataReader = command.ExecuteReader()



        If reader.HasRows Then
            While (reader.Read())
                retVal += "<div class=""comment""><div class=""top"">"
                retVal += "<span class=""uname"">" + CStr(reader.Item("username")) + "</span></div>"
                retVal += "<div class=""body"">" + CStr(reader.Item("body")) + "</div></div>"

            End While


            reader.Close()
        End If

        dbc.Close()

        Return retVal + "</div>"

    End Function

    Function listTenNewestBooks() As ArrayList
        Dim retVal As ArrayList = New ArrayList()
        Dim queryString As String = "SELECT TOP 10 Book.Title, Author.name AS Author, Book.ISBN, Book.Description, Book.year_published AS Published FROM Book, Author WHERE Book.author_id = Author.ID ORDER BY Published DESC"
        Dim command As New OleDbCommand(queryString, dbc)
        dbc.Open()
        Dim reader As OleDbDataReader = command.ExecuteReader()

        If reader.HasRows Then
            reader.Read()



            reader.Close()

        Else


        End If

        dbc.Close()
        Return retVal
    End Function




End Module
